//*******************************//
//<xml fileName="DUserDao.java" ?>
//<author>申鱼川</author>
//<dateTime>2014-02-23</dateTime>
//</xml>
//*******************************//
package youleche.com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import youleche.com.connection.JdbcHelper;
import youleche.com.idao.IUserDao;
import youleche.com.model.MUser;

/**
 * 用户类数据访问类
 * @author Administrator
 *
 */
public class DUserDao implements IUserDao {

	/**
	 * 用户登录
	 *@author qw
	 *@version 创建时间:2014-2-28上午10:37:03
	 * @throws SQLException 
	 *@see youleche.com.idao.IUserDao#login(youleche.com.model.MUser, java.sql.Connection)
	 */
	@Override
	public boolean login(MUser user, Connection con) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select userName from userInfo where (userName='"+user.getUserName()+"' or phoneNum='"+user.getPhoneNum()+"' or email='"+user.getEmail()+"') and userPassword='"+user.getUserPassword()+"' and isDelete=0 limit 1;";
			
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				if(!("").equals(rs.getString(1))){
					return true;
				};
				
			}
			
			return false;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	
	/**
	 * 用户注注
	 *@author 
	 *@version 创建时间:2014-2-28上午10:37:35
	 *@see youleche.com.idao.IUserDao#register(youleche.com.model.MUser, java.sql.Connection)
	 */
	@Override
	public int register(MUser user, Connection con) throws Exception{
		PreparedStatement ps = null;
		try{
			String sql = "insert userinfo(id,email,userName,phoneNum) value('" + user.getId() + "','" + user.getEmail() + "','"+user.getUserName()+"','"+user.getPhoneNum()+"');";
			ps = con.prepareStatement(sql);
			return ps.executeUpdate();
		}finally{
			JdbcHelper.free(null, ps);
		}
	}

	/**
	 * 通过邮箱获取用户名
	 * @param email 邮箱
	 * @param con 数据库连接
	 * @return 返回用户名
	 * @throws SQLException 
	 */
	@Override
	public String getUserNameByEmailAccount(String email, Connection con) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select userName from userInfo where email='"+email+"' limit 1;";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				return rs.getString(1);			
			}
			
			return "";
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 通过电话号码获取用户名
	 * @param phoneNum 电话号码
	 * @param con 数据库连接
	 * @return 返回用户名
	 */
	@Override
	public String getUserNameByPhoneNum(String phoneNum, Connection con) throws Exception  {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select userName from userInfo where phoneNum='"+phoneNum+"' limit 1;";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				return rs.getString(1);			
			}
			
			return "";
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}


	
	/**
	 * 获取商家总数
	 * @param con 数据库连接
	 * @return 返回商家总数
	 * @throws Exception 异常
	 */
	@Override
	public int getSellerForCount(Connection con) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select count(userName) from userInfo where IsDelete=0 and a4='1'";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			String count = "";
			while(rs.next()) {
				count = rs.getString(1);
				break;
			}
			
			if(count != null){
				return Integer.parseInt(count);
			}
			
			return 0;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 判断管理员是否登陆
	 * @param con 数据库连接
	 * @param user 用户信息
	 * @return 返回登陆是否成功 true-成功
	 * @throws Exception 异常
	 */
	@Override
	public boolean adminLogin(Connection con, MUser user) throws Exception {
		
		return false;
	}


	/**
	 * 判断用户是否注册
	 * @param con 数据库连接
	 * @param email 邮件地址
	 * @return 返回是否注册  true-注册
	 */
	@Override
	public boolean isRegitered(Connection con, String email)  throws Exception{
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "SELECT email from userinfo where email='"+email+"' and isdelete=0 limit 1;";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			String result = "";
			if(rs != null){
				while(rs.next()){
					result = rs.getString(1);
					break;
				}
			}
			
			return "".equals(result) ? false : true;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 激活用户信息
	 * @param con 数据库连接
	 * @param user 用户名
	 * @return 返回影响的行数
	 * @throws Exception 异常
	 */
	@Override
	public int actUserInfo(Connection con, MUser user) throws Exception {
		PreparedStatement ps = null;
		try{
			String sql = "update userinfo set userName='"+user.getUserName()+"',A3='2', phoneNum='"+user.getPhoneNum()+"',A10='"+user.getA10()+"', userPassword='"+user.getUserPassword()+"' where email='"+user.getEmail()+"' and isDelete=0 limit 1;";
			ps = con.prepareStatement(sql);
			
			return ps.executeUpdate();
		}finally{
			JdbcHelper.free(null, ps);
		}
	}

	/**
	 * 根据用户名获取用户
	 * @param con 数据库连接
	 * @param userName 用户名
	 * @return 用户信息
	 * @throws Exception 异常
	 */
	@Override
	public MUser getUserByUserName(Connection con, String userName)
			throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "SELECT " +
					"id,userName,phoneNum,userPassword,email," +
					"sex,lastVisit,registeTime,isDelete,A1," +
					"A2,A3,A4,A5,A6," +
					"A7,A8,A9,A10 " +
					"FROM userinfo " +
					"where userName='"+userName+"' and isDelete='0' limit 1;";
			ps = con.prepareStatement(sql);
			rs= ps.executeQuery();
			MUser user = null;
			if(rs != null){
				while(rs.next()){
					user = new MUser();
					user.setId(rs.getString(1));
					user.setUserName(rs.getString(2));
					user.setPhoneNum(rs.getString(3));
					user.setUserPassword(rs.getString(4));
					user.setEmail(rs.getString(5));
					user.setSex(rs.getString(6));
					user.setLastVisit(rs.getString(7));
					user.setRegisteTime(rs.getString(8));
					user.setIsDelete(rs.getString(9));
					user.setA1(rs.getString(10));
					user.setA2(rs.getString(11));
					user.setA3(rs.getString(12));
					user.setA4(rs.getString(13));
					user.setA5(rs.getString(14));
					user.setA6(rs.getString(15));
					user.setA7(rs.getString(16));
					user.setA8(rs.getString(17));
					user.setA9(rs.getString(18));
					user.setA10(rs.getString(19));
					break;
				}
			}
			
			return user;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 更新用户密码通过用户名
	 * @param con 数据库连接
	 * @param userName 用户名
	 * @return 返回影响的行数
	 * @throws Exception
	 */
	@Override
	public int updatePasswordByUserName(Connection con, String userName, String pass)
			throws Exception {
		PreparedStatement ps = null;
		try{
			String sql ="update userinfo set userPassword='"+pass+"' where isdelete=0 and username='"+userName+"' limit 1";
			ps = con.prepareStatement(sql);
			return ps.executeUpdate();
		}finally{
			JdbcHelper.free(null, ps);
		}
	}

	/**
	 * 判断是否是商家
	 * @param con 数据库连接
	 * @param email 邮件
	 * @return 返回是否是商家 true-是
	 * @throws Exception
	 */
	@Override
	public boolean isSellserByEmail(Connection con, String email)
			throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select A4 from userinfo where email='"+ email +"' and isdelete=0 limit 1;";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs != null){
				while(rs.next()){
					//a4为0或者空，那么不是商家， 为1为商家
					if("1".equals(rs.getString(1))){
						return true;
					}
					
					break;
				}
			}
			
			return false;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 变成商家
	 * @param con 数据库连接
	 * @param email 邮件
	 * @return 返回影响的行数
	 * @throws Exception 异常
	 */
	@Override
	public int becomeSellerByEmail(Connection con, String email)
			throws Exception {
		PreparedStatement ps = null;
		try{
			String sql = "update userinfo set a4='1' where isdelete=0 and email='"+email+"' limit 1";
			ps = con.prepareStatement(sql);
			
			return ps.executeUpdate();
		}finally{
			JdbcHelper.free(null, ps);
		}
	}

	/**
	 * 修改电话号码
	 * @param con 数据库连接
	 * @param email 邮件
	 * @param phoneNum 电话
	 * @return 返回影响的行数
	 * @throws Exception 异常
	 */
	@Override
	public int updatePhoneByEmail(Connection con, String email, String phoneNum)
			throws Exception {
		PreparedStatement ps = null;
		try{
			String sql = "update userinfo set phoneNum='" + phoneNum + "' where isdelete=0 and email='"+email+"' limit 1";
			ps = con.prepareStatement(sql);
			
			return ps.executeUpdate();
		}finally{
			JdbcHelper.free(null, ps);
		}
	}

	/**
	 * 申请变成商家
	 * @param con 数据库连接
	 * @param email 邮件
	 * @return 返回影响的行数
	 * @throws Exception 异常
	 */
	@Override
	public int applyBecomeSellerByEmail(Connection con, String email)
			throws Exception {
		PreparedStatement ps = null;
		try{
			String sql = "update userinfo set a4='1.5' where isdelete=0 and email='"+email+"' limit 1";
			ps = con.prepareStatement(sql);
			
			return ps.executeUpdate();
		}finally{
			JdbcHelper.free(null, ps);
		}
	}

	/**
	 * 判断是否申请了商家
	 * @param con 数据库连接
	 * @param email 邮件
	 * @return 返回是否是商家 true-是
	 * @throws Exception
	 */
	@Override
	public boolean isApplySellserByEmail(Connection con, String email)
			throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select A4 from userinfo where email='"+ email +"' and isdelete=0 limit 1;";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs != null){
				while(rs.next()){
					//为1.5为申请商家
					if("1.5".equals(rs.getString(1))){
						return true;
					}
					
					break;
				}
			}
			
			return false;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 获取商家列表
	 * @param con 数据库连接
	 * @startIndex 数据表开始条数
	 * @endIndex 每次获取多少条数
	 * @return 返回商家列表
	 * @throws Exception 异常
	 */
	@Override
	public List<MUser> getSellerList(Connection con, String startIndex,
			String endIndex) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select id,userName,phoneNum,email,sex,lastVisit,registeTime from userinfo where isdelete=0 and a4='1' limit " + startIndex + ", "+endIndex+";";
			ps = con.prepareStatement(sql);
			rs= ps.executeQuery();
			MUser user = null;
			List<MUser> list = null;
			if(rs != null){
				list = new LinkedList<MUser>();
				while(rs.next()){
					user = new MUser();
					user.setId(rs.getString(1));
					user.setUserName(rs.getString(2));
					user.setPhoneNum(rs.getString(3));
					user.setEmail(rs.getString(4));
					user.setSex(rs.getString(5));
					user.setLastVisit(rs.getString(6));
					user.setRegisteTime(rs.getString(7));
					
					list.add(user);
				
				}
			}
			
			return list;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 获取申请商家列表
	 * @param con 数据库连接
	 * @startIndex 数据表开始条数
	 * @endIndex 每次获取多少条数
	 * @return 返回申请商家列表
	 * @throws Exception 异常
	 */
	@Override
	public List<MUser> getApplySellerList(Connection con, String startIndex,
			String endIndex) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select id,userName,phoneNum,email,sex,lastVisit,registeTime from userinfo where isdelete=0 and a4='1.5' limit " + startIndex + ", "+endIndex+";";
			ps = con.prepareStatement(sql);
			rs= ps.executeQuery();
			MUser user = null;
			List<MUser> list = null;
			if(rs != null){
				list = new LinkedList<MUser>();
				while(rs.next()){
					user = new MUser();
					user.setId(rs.getString(1));
					user.setUserName(rs.getString(2));
					user.setPhoneNum(rs.getString(3));
					user.setEmail(rs.getString(4));
					user.setSex(rs.getString(5));
					user.setLastVisit(rs.getString(6));
					user.setRegisteTime(rs.getString(7));
					
					list.add(user);
				
				}
			}
			
			return list;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

	/**
	 * 获取商家总数
	 * @param con 数据库连接
	 * @param a4_status a4商家状态（1-商家，1.5-申请中商家）
	 * @return 返回商家总数
	 * @throws Exception 异常
	 */
	@Override
	public int getSellerCountByA4_Status(Connection con, String a4_status)
			throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			
			String sql = "select count(userName) from userInfo where IsDelete=0 and a4='"+a4_status+"'";
			ps = con.prepareStatement(sql);
			rs = null;
			rs = ps.executeQuery();
			String count = "";
			while(rs.next()) {
				count = rs.getString(1);
				break;
			}
			
			if(count != null){
				return Integer.parseInt(count);
			}
			
			return 0;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}


	/**
	 * 修改用户信息
	 * @param con 数据库连接
	 * @param user 用户信息
	 * @return 返回修改影响的行数
	 * @throws Exception 异常 
	 */
	@Override
	public int updateUserInfoById(Connection con, MUser user) throws Exception {
		PreparedStatement ps = null;
		try{
			String sql = "update userInfo set a5='"+user.getA5()+"',userName='"+user.getUserName()+"', phoneNum='"+user.getPhoneNum()+"' where id='"+user.getId()+"' and isdelete=0 limit 1;";
			ps = con.prepareStatement(sql);
			return ps.executeUpdate();
		}finally{
			JdbcHelper.free(null, ps);
		}
	}


	/**
	 * 根据用户ID得到用户信息
	 * @param con 数据库连接
	 * @param id id
	 * @return 返回用户信息
	 * @throws Exception 异常
	 */
	@Override
	public MUser getUserById(Connection con, String id) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "SELECT " +
					"id,userName,phoneNum,userPassword,email," +
					"sex,lastVisit,registeTime,isDelete,A1," +
					"A2,A3,A4,A5,A6," +
					"A7,A8,A9,A10 " +
					"FROM userinfo " +
					"where id='"+id+"' and isDelete='0' limit 1;";
			ps = con.prepareStatement(sql);
			rs= ps.executeQuery();
			MUser user = null;
			if(rs != null){
				while(rs.next()){
					user = new MUser();
					user.setId(rs.getString(1));
					user.setUserName(rs.getString(2));
					user.setPhoneNum(rs.getString(3));
					user.setUserPassword(rs.getString(4));
					user.setEmail(rs.getString(5));
					user.setSex(rs.getString(6));
					user.setLastVisit(rs.getString(7));
					user.setRegisteTime(rs.getString(8));
					user.setIsDelete(rs.getString(9));
					user.setA1(rs.getString(10));
					user.setA2(rs.getString(11));
					user.setA3(rs.getString(12));
					user.setA4(rs.getString(13));
					user.setA5(rs.getString(14));
					user.setA6(rs.getString(15));
					user.setA7(rs.getString(16));
					user.setA8(rs.getString(17));
					user.setA9(rs.getString(18));
					user.setA10(rs.getString(19));
					break;
				}
			}
			
			return user;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}


	/**
	 * 根据用户openID得到用户信息(openID即A10)
	 * @param con 数据库连接
	 * @param openID openID
	 * @return 返回用户信息
	 * @throws Exception 异常
	 */
	@Override
	public MUser getUserByOpenID_A10(Connection con, String openId)
			throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "SELECT " +
					"id,userName,phoneNum,userPassword,email," +
					"sex,lastVisit,registeTime,isDelete,A1," +
					"A2,A3,A4,A5,A6," +
					"A7,A8,A9,A10 " +
					"FROM userinfo " +
					"where A10='"+openId+"' and isDelete='0' limit 1;";
			ps = con.prepareStatement(sql);
			rs= ps.executeQuery();
			MUser user = null;
			if(rs != null){
				while(rs.next()){
					user = new MUser();
					user.setId(rs.getString(1));
					user.setUserName(rs.getString(2));
					user.setPhoneNum(rs.getString(3));
					user.setUserPassword(rs.getString(4));
					user.setEmail(rs.getString(5));
					user.setSex(rs.getString(6));
					user.setLastVisit(rs.getString(7));
					user.setRegisteTime(rs.getString(8));
					user.setIsDelete(rs.getString(9));
					user.setA1(rs.getString(10));
					user.setA2(rs.getString(11));
					user.setA3(rs.getString(12));
					user.setA4(rs.getString(13));
					user.setA5(rs.getString(14));
					user.setA6(rs.getString(15));
					user.setA7(rs.getString(16));
					user.setA8(rs.getString(17));
					user.setA9(rs.getString(18));
					user.setA10(rs.getString(19));
					break;
				}
			}
			
			return user;
		}finally{
			JdbcHelper.free(rs, ps);
		}
	}

}
